package com.tangdi.Dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.tangdi.Bean.CZGZ_DWXX;
import com.tangdi.Bean.CZGZ_DWXX_TOTAL;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.Bean.DW_DM_MC;
import com.tangdi.common.DBConnect;

public class GongziDWImpl {
	private Logger logger = Logger.getLogger(GongziDWImpl.class);
	Connection con=DBConnect.getInstance().getConnection();
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	public void releaseConn(){
		try{
			this.con.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public List<CZGZ_DWXX> listGZDWReport(String month){
		List<CZGZ_DWXX> list = new ArrayList<CZGZ_DWXX>();
		String sql="SELECT TRIM(BAT_NO) AS BAT_NO ,TRIM(THD_BAT_NO) AS THD_BAT_NO ,TRIM(YF) AS YF ,TRIM(DW_DM) AS DW_DM ,TRIM(DW_MC) AS DW_MC ,TRIM(BAO_ZZ) AS BAO_ZZ ,"
				+ "COALESCE(FORMAT(TO_NUMBER(TRIM(TF_RS)),2),'0') AS TF_RS ,"
				+ "COALESCE(FORMAT(TO_NUMBER(TRIM(YF_GZ)),2),'0') AS YF_GZ ,"
				+ "COALESCE(FORMAT(TO_NUMBER(TRIM(DF_GZ)),2),'0') AS DF_GZ ,"
				+ "COALESCE(FORMAT(TO_NUMBER(TRIM(SF_GZ)),2),'0') AS SF_GZ ,"
				+ "TRIM(GZ_LX) AS GZ_LX ,TRIM(GZ_LX_MC) AS GZ_LX_MC ,TRIM(DW_REMARK) AS DW_REMARK ,TRIM(TP_BAT_NO) AS TP_BAT_NO ,TRIM(YEAR) AS YEAR ,TRIM(MONTH) AS MONTH ,"
				+ "TRIM(RCN_CDE) AS RCN_CDE  FROM CZGZ_DWXX "
				+ "WHERE TRIM(YF) = '"+ month  +"'";
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_DWXX lb = new CZGZ_DWXX();
				lb.setBAT_NO(rs.getString("BAT_NO"));
				lb.setTHD_BAT_NO(rs.getString("THD_BAT_NO"));
				lb.setYF(rs.getString("YF"));
				lb.setDW_DM(rs.getString("DW_DM"));
				lb.setDW_MC(rs.getString("DW_MC"));
				lb.setBAO_ZZ(rs.getString("BAO_ZZ"));
				lb.setTF_RS(rs.getString("TF_RS"));
				lb.setYF_GZ(rs.getString("YF_GZ"));
				lb.setDF_GZ(rs.getString("DF_GZ"));
				lb.setSF_GZ(rs.getString("SF_GZ"));
				lb.setGZ_LX(rs.getString("GZ_LX"));
				lb.setGZ_LX_MC(rs.getString("GZ_LX_MC"));
				lb.setDW_REMARK(rs.getString("DW_REMARK"));
				lb.setTP_BAT_NO(rs.getString("TP_BAT_NO"));
				lb.setYEAR(rs.getString("YEAR"));
				lb.setMONTH(rs.getString("MONTH"));
				lb.setRCN_CDE(rs.getString("RCN_CDE"));
				list.add(lb);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return list;
	}
	
	public CZGZ_DWXX_TOTAL getGZDWCount(String month){
		CZGZ_DWXX_TOTAL dwcount = new CZGZ_DWXX_TOTAL();
		String sql="SELECT YF,COALESCE(FORMAT(TO_NUMBER(COUNT(1)),0),'0') AS DWCOUNT,"
				+ "COALESCE(FORMAT(TO_NUMBER(SUM(TF_RS)),2),'0') AS TF_RS_TOTAL,"
				+ "COALESCE(FORMAT(TO_NUMBER(SUM(YF_GZ)),2),'0') AS YF_GZ_TOTAL,"
				+ "COALESCE(FORMAT(TO_NUMBER(SUM(DF_GZ)),2),'0') AS DF_GZ_TOTAL,"
				+ "COALESCE(FORMAT(TO_NUMBER(SUM(SF_GZ)),2),'0') AS SF_GZ_TOTAL"
				+ " FROM  CZGZ_DWXX WHERE TRIM(YF) = '"+ month  +"'"
				+ " GROUP BY YF";
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				dwcount.setYF(rs.getString("YF"));
				dwcount.setDWCOUNT(rs.getString("DWCOUNT"));
				dwcount.setTF_RS_TOTAL(rs.getString("TF_RS_TOTAL"));
				dwcount.setYF_GZ_TOTAL(rs.getString("YF_GZ_TOTAL"));
				dwcount.setDF_GZ_TOTAL(rs.getString("DF_GZ_TOTAL"));
				dwcount.setSF_GZ_TOTAL(rs.getString("SF_GZ_TOTAL"));
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return dwcount;
	}
	
	public List<CZGZ_ZGGZMX> findListGZMX(String month,String person_Name,String person_Bank,String person_Idno){
		List<CZGZ_ZGGZMX> list = new ArrayList<CZGZ_ZGGZMX>();
		StringBuffer sql= new StringBuffer("SELECT trim(SEQ_NO) AS SEQ_NO,trim(TP_BAT_NO) AS TP_BAT_NO,trim(BAT_NO) AS BAT_NO,trim(THD_BAT_NO) AS THD_BAT_NO,trim(AP_SYS_CDE) AS AP_SYS_CDE,trim(PAY_BNK) AS PAY_BNK,trim(PER_BNK_NO) AS PER_BNK_NO,"
				+ "trim(BNK_FLG) AS BNK_FLG,trim(PER_AC) AS PER_AC,trim(PER_OLD_AC) AS PER_OLD_AC,trim(NME) AS NME,trim(TXN_AMT) AS TXN_AMT,trim(REL_TXN_AMT) AS REL_TXN_AMT,trim(DTL_CDE) AS DTL_CDE,trim(DTL_MSG) AS DTL_MSG,"
				+ "trim(DTL_STS) AS DTL_STS,trim(BR) AS BR,trim(TLR) AS TLR,trim(BUS_KND) AS BUS_KND,trim(BUS_TYP) AS BUS_TYP,trim(PWD_CHK_FLG) AS PWD_CHK_FLG,trim(TXN_DTE) AS TXN_DTE,trim(BSMR) AS BSMR,trim(YF) AS YF,"
				+ "trim(DW_DM) AS DW_DM,trim(DW_MC) AS DW_MC,trim(GZ_LB_DM) AS GZ_LB_DM,trim(BM_DM) AS BM_DM,trim(RY_DM) AS RY_DM,trim(ORD_NO) AS ORD_NO,trim(KM_MC) AS KM_MC,trim(ID_NO) AS ID_NO,"
				+ "trim(ACCOUNT_FLG) AS ACCOUNT_FLG,trim(YF_GZ) AS YF_GZ,trim(DK_XJ) AS DK_XJ,trim(SF_TXN_AMT) AS SF_TXN_AMT,trim(GZ_LX) AS GZ_LX,trim(GZ_LX_MC) AS GZ_LX_MC,trim(PAY_SEQ_NO) AS PAY_SEQ_NO,"
				+ "trim(PAY_LOG_NO) AS PAY_LOG_NO,trim(RMK1) AS RMK1,trim(RMK2) AS RMK2,trim(B001) AS B001,trim(B002) AS B002,trim(B003) AS B003,trim(B004) AS B004,trim(B005) AS B005,trim(B006) AS B006,"
				+ "trim(B007) AS B007,trim(B008) AS B008,trim(B009) AS B009,trim(B010) AS B010,trim(B011) AS B011,trim(B012) AS B012,trim(B013) AS B013,trim(B014) AS B014,trim(B015) AS B015,trim(B016) AS B016,"
				+ "trim(B017) AS B017,trim(B018) AS B018,trim(B019) AS B019,trim(B020) AS B020,trim(B021) AS B021,trim(B022) AS B022,trim(B023) AS B023,trim(B024) AS B024,trim(B025) AS B025,trim(B026) AS B026,"
				+ "trim(B027) AS B027,trim(B028) AS B028,trim(B029) AS B029,trim(B030) AS B030,trim(B031) AS B031,trim(B032) AS B032,trim(B033) AS B033,trim(B034) AS B034,trim(B035) AS B035,trim(B036) AS B036,"
				+ "trim(B037) AS B037,trim(B038) AS B038,trim(B039) AS B039,trim(B040) AS B040,trim(B041) AS B041,trim(B042) AS B042,trim(B043) AS B043,trim(B044) AS B044,trim(B045) AS B045,trim(B046) AS B046,"
				+ "trim(B047) AS B047,trim(B048) AS B048,trim(B049) AS B049,trim(B050) AS B050,trim(B051) AS B051,trim(B052) AS B052,trim(B053) AS B053,trim(B054) AS B054,trim(B055) AS B055,trim(B056) AS B056,"
				+ "trim(B057) AS B057,trim(B058) AS B058,trim(B059) AS B059,trim(B060) AS B060,trim(B061) AS B061,trim(B062) AS B062,trim(B063) AS B063,trim(B064) AS B064,trim(B065) AS B065,trim(B066) AS B066,"
				+ "trim(B067) AS B067,trim(B068) AS B068,trim(B069) AS B069,trim(B070) AS B070,trim(B071) AS B071,trim(B072) AS B072,trim(B073) AS B073,trim(B074) AS B074,trim(B075) AS B075,trim(B076) AS B076,"
				+ "trim(B077) AS B077,trim(B078) AS B078,trim(B079) AS B079,trim(B080) AS B080,trim(B081) AS B081,trim(B082) AS B082,trim(B083) AS B083,trim(B084) AS B084,trim(B085) AS B085,trim(B086) AS B086,"
				+ "trim(B087) AS B087,trim(B088) AS B088,trim(B089) AS B089,trim(B090) AS B090,trim(FFFS) AS FFFS,trim(DHYB) AS DHYB,trim(DHYH) AS DHYH,trim(DHDZ) AS DHDZ,trim(DHKH) AS DHKH,trim(JT_TXN_AMT) AS JT_TXN_AMT,"
				+ "trim(JT_CUS_AC) AS JT_CUS_AC,trim(JT_SALARY_YEAR) AS JT_SALARY_YEAR,trim(JT_SALARY_MONTH) AS JT_SALARY_MONTH,trim(GZ_XZ) AS GZ_XZ,trim(S_SEQ_NO) AS S_SEQ_NO,trim(RCN_CDE) AS RCN_CDE ,"
				+ "trim(BM_MC) AS BM_MC, trim(BMN_XH) AS BMN_XH, trim(KM_DM) AS KM_DM, trim(WORK_STATUS_CDE) AS WORK_STATUS_CDE, trim(WORK_STATUS_NME) AS WORK_STATUS_NME "
				+ " FROM CZGZ_ZGGZMX WHERE 1 = 1 ");
		if(!month.equals("")){
			sql.append(" AND trim(YF) = '"+ month +"'");
		}
		if(!person_Name.equals("")){
			sql.append(" AND trim(NME) = '"+ person_Name +"'");
		}
		if(!person_Bank.equals("")){
			sql.append(" AND trim(PER_AC) = '"+ person_Bank +"'");
		}
		if(!person_Idno.equals("")){
			sql.append(" AND trim(ID_NO) = '"+ person_Idno +"'");
		}
		sql.append(" ORDER BY AP_SYS_CDE, DW_DM, GZ_LB_DM ");
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_ZGGZMX mx = new CZGZ_ZGGZMX();
 
				mx.setSEQ_NO(rs.getString("SEQ_NO"));
				mx.setTP_BAT_NO(rs.getString("TP_BAT_NO"));
				mx.setBAT_NO(rs.getString("BAT_NO"));
				mx.setTHD_BAT_NO(rs.getString("THD_BAT_NO"));
				mx.setAP_SYS_CDE(rs.getString("AP_SYS_CDE"));
				mx.setPAY_BNK(rs.getString("PAY_BNK"));
				mx.setPER_BNK_NO(rs.getString("PER_BNK_NO"));
				mx.setBNK_FLG(rs.getString("BNK_FLG"));
				mx.setPER_AC(rs.getString("PER_AC"));
				mx.setPER_OLD_AC(rs.getString("PER_OLD_AC"));
				mx.setNME(rs.getString("NME"));
				mx.setTXN_AMT(rs.getString("TXN_AMT")); 
				mx.setREL_TXN_AMT(rs.getString("REL_TXN_AMT")); 
				mx.setDTL_CDE(rs.getString("DTL_CDE"));
				mx.setDTL_MSG(rs.getString("DTL_MSG"));
				mx.setDTL_STS(rs.getString("DTL_STS"));
				mx.setBR(rs.getString("BR"));
				mx.setTLR(rs.getString("TLR"));
				mx.setBUS_KND(rs.getString("BUS_KND"));
				mx.setBUS_TYP(rs.getString("BUS_TYP"));
				mx.setPWD_CHK_FLG(rs.getString("PWD_CHK_FLG"));
				mx.setTXN_DTE(rs.getString("TXN_DTE"));
				mx.setBSMR(rs.getString("BSMR"));
				mx.setYF(rs.getString("YF"));
				mx.setDW_DM(rs.getString("DW_DM"));
				mx.setDW_MC(rs.getString("DW_MC"));
				mx.setGZ_LB_DM(rs.getString("GZ_LB_DM"));
				mx.setBM_DM(rs.getString("BM_DM"));
				mx.setRY_DM(rs.getString("RY_DM"));
				mx.setORD_NO(rs.getString("ORD_NO"));
				mx.setKM_MC(rs.getString("KM_MC"));
				mx.setID_NO(rs.getString("ID_NO"));
				mx.setACCOUNT_FLG(rs.getString("ACCOUNT_FLG"));
				mx.setYF_GZ(rs.getString("YF_GZ")); 
				mx.setDK_XJ(rs.getString("DK_XJ")); 
				mx.setSF_TXN_AMT(rs.getString("SF_TXN_AMT")); 
				mx.setGZ_LX(rs.getString("GZ_LX"));
				mx.setGZ_LX_MC(rs.getString("GZ_LX_MC"));
				mx.setPAY_SEQ_NO(rs.getString("PAY_SEQ_NO"));
				mx.setPAY_LOG_NO(rs.getString("PAY_LOG_NO"));
				mx.setRMK1(rs.getString("RMK1"));
				mx.setRMK2(rs.getString("RMK2"));
				mx.setB001(rs.getString("B001")); 
				mx.setB002(rs.getString("B002")); 
				mx.setB003(rs.getString("B003")); 
				mx.setB004(rs.getString("B004")); 
				mx.setB005(rs.getString("B005")); 
				mx.setB006(rs.getString("B006")); 
				mx.setB007(rs.getString("B007")); 
				mx.setB008(rs.getString("B008")); 
				mx.setB009(rs.getString("B009")); 
				mx.setB010(rs.getString("B010")); 
				mx.setB011(rs.getString("B011")); 
				mx.setB012(rs.getString("B012")); 
				mx.setB013(rs.getString("B013")); 
				mx.setB014(rs.getString("B014")); 
				mx.setB015(rs.getString("B015")); 
				mx.setB016(rs.getString("B016")); 
				mx.setB017(rs.getString("B017")); 
				mx.setB018(rs.getString("B018")); 
				mx.setB019(rs.getString("B019")); 
				mx.setB020(rs.getString("B020")); 
				mx.setB021(rs.getString("B021")); 
				mx.setB022(rs.getString("B022")); 
				mx.setB023(rs.getString("B023")); 
				mx.setB024(rs.getString("B024")); 
				mx.setB025(rs.getString("B025")); 
				mx.setB026(rs.getString("B026")); 
				mx.setB027(rs.getString("B027")); 
				mx.setB028(rs.getString("B028")); 
				mx.setB029(rs.getString("B029")); 
				mx.setB030(rs.getString("B030")); 
				mx.setB031(rs.getString("B031")); 
				mx.setB032(rs.getString("B032")); 
				mx.setB033(rs.getString("B033")); 
				mx.setB034(rs.getString("B034")); 
				mx.setB035(rs.getString("B035")); 
				mx.setB036(rs.getString("B036")); 
				mx.setB037(rs.getString("B037")); 
				mx.setB038(rs.getString("B038")); 
				mx.setB039(rs.getString("B039")); 
				mx.setB040(rs.getString("B040")); 
				mx.setB041(rs.getString("B041")); 
				mx.setB042(rs.getString("B042")); 
				mx.setB043(rs.getString("B043")); 
				mx.setB044(rs.getString("B044")); 
				mx.setB045(rs.getString("B045")); 
				mx.setB046(rs.getString("B046")); 
				mx.setB047(rs.getString("B047")); 
				mx.setB048(rs.getString("B048")); 
				mx.setB049(rs.getString("B049")); 
				mx.setB050(rs.getString("B050")); 
				mx.setB051(rs.getString("B051")); 
				mx.setB052(rs.getString("B052")); 
				mx.setB053(rs.getString("B053")); 
				mx.setB054(rs.getString("B054")); 
				mx.setB055(rs.getString("B055")); 
				mx.setB056(rs.getString("B056")); 
				mx.setB057(rs.getString("B057")); 
				mx.setB058(rs.getString("B058")); 
				mx.setB059(rs.getString("B059")); 
				mx.setB060(rs.getString("B060")); 
				mx.setB061(rs.getString("B061")); 
				mx.setB062(rs.getString("B062")); 
				mx.setB063(rs.getString("B063")); 
				mx.setB064(rs.getString("B064")); 
				mx.setB065(rs.getString("B065")); 
				mx.setB066(rs.getString("B066")); 
				mx.setB067(rs.getString("B067")); 
				mx.setB068(rs.getString("B068")); 
				mx.setB069(rs.getString("B069")); 
				mx.setB070(rs.getString("B070")); 
				mx.setB071(rs.getString("B071")); 
				mx.setB072(rs.getString("B072")); 
				mx.setB073(rs.getString("B073")); 
				mx.setB074(rs.getString("B074")); 
				mx.setB075(rs.getString("B075")); 
				mx.setB076(rs.getString("B076")); 
				mx.setB077(rs.getString("B077")); 
				mx.setB078(rs.getString("B078")); 
				mx.setB079(rs.getString("B079")); 
				mx.setB080(rs.getString("B080")); 
				mx.setB081(rs.getString("B081")); 
				mx.setB082(rs.getString("B082")); 
				mx.setB083(rs.getString("B083")); 
				mx.setB084(rs.getString("B084")); 
				mx.setB085(rs.getString("B085")); 
				mx.setB086(rs.getString("B086")); 
				mx.setB087(rs.getString("B087")); 
				mx.setB088(rs.getString("B088")); 
				mx.setB089(rs.getString("B089")); 
				mx.setB090(rs.getString("B090")); 
				mx.setFFFS(rs.getString("FFFS"));
				mx.setDHYB(rs.getString("DHYB"));
				mx.setDHYH(rs.getString("DHYH"));
				mx.setDHDZ(rs.getString("DHDZ"));
				mx.setDHKH(rs.getString("DHKH"));
				mx.setJT_TXN_AMT(rs.getString("JT_TXN_AMT")); 
				mx.setJT_CUS_AC(rs.getString("JT_CUS_AC"));
				mx.setJT_SALARY_YEAR(rs.getString("JT_SALARY_YEAR"));
				mx.setJT_SALARY_MONTH(rs.getString("JT_SALARY_MONTH"));
				mx.setGZ_XZ(rs.getString("GZ_XZ"));
				mx.setS_SEQ_NO(rs.getString("S_SEQ_NO"));
				mx.setRCN_CDE(rs.getString("RCN_CDE"));
				mx.setBM_MC(rs.getString("BM_MC"));
				mx.setBMN_XH(rs.getString("BMN_XH"));
				mx.setKM_DM(rs.getString("KM_DM"));
				mx.setWORK_STATUS_CDE(rs.getString("WORK_STATUS_CDE"));
				mx.setWORK_STATUS_NME(rs.getString("WORK_STATUS_NME"));

				list.add(mx);
				
				}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
								}
					}
		

		return list;
	}
	
	//根据月份、单位代码、工资类别以及组装的select 列名，来查询工资明细
	public List<DW_DM_MC> getDWInfo(String month){
		List<DW_DM_MC> dw_dm_mcList = new ArrayList<DW_DM_MC>();
		StringBuffer sql_select = new StringBuffer("SELECT DISTINCT TRIM(DW_DM) AS DW_DM,TRIM(DW_MC) AS DW_MC FROM CZGZ_ZGGZMX WHERE TRIM(YF) = '"+ month +"'");
		sql_select.append("ORDER BY TRIM(DW_DM)");
		String sql_run = sql_select.toString();
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				DW_DM_MC dw_dm_mc= new DW_DM_MC();
				dw_dm_mc.setDM(rs.getString("DW_DM"));
				dw_dm_mc.setMC(rs.getString("DW_MC"));
				dw_dm_mcList.add(dw_dm_mc);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return dw_dm_mcList;
	}
}
